package DB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author Sibrand
 */
public class RelationManager extends DB {

    public RelationManager(String dbName, String userName, String password) {
        super(dbName, userName, password);
    }

    public RelationManager(Connection c) {
        conn = c;
    }

    public static enum REL {

        FAMILY, FRIEND, OTHER
    };

    public String getRelationBetween(String user1, String user2) {
        if (isConnected() && usernameExists(user2) && usernameExists(user1)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT type FROM Relations WHERE accepted=1 AND ((username1= ? AND username2= ? ) OR (username2= ? AND username1= ?))");
                pstmt.setString(1, user1);
                pstmt.setString(2, user2);
                pstmt.setString(3, user1);
                pstmt.setString(4, user2);
                ResultSet rs = pstmt.executeQuery();
                if (rs.next()) {
                    return rs.getString(1);
                } else {
                    return "OTHER";
                }
            } catch (SQLException ex) {
                return "OTHER";
            }
        } else {
            return "OTHER";
        }
    }

    public String getRelationWithGroup(String[] others, String user) {
        String rel = "OTHER";

        for (int i = 0; i < others.length; ++i) {
            String temp = getRelationBetween(others[i], user);

            if (temp.compareTo("FAMILY") == 0) {
                return "FAMILY";
            }
            if (temp.compareTo("FRIEND") == 0) {
                rel = "FRIEND";
            }
        }

        return rel;
    }

    public String getPendingRelationBetween(String user1, String user2) {
        if (isConnected() && usernameExists(user2) && usernameExists(user1)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT type FROM Relations WHERE processed=0 AND username1= ? AND accepted=0 AND username2= ?");
                pstmt.setString(1, user1);
                pstmt.setString(2, user2);
                ResultSet rs = pstmt.executeQuery();

                if (rs.next()) {
                    return rs.getString(1);
                } else {
                    pstmt = conn.prepareStatement("SELECT type FROM Relations WHERE processed=0 AND username2= ? AND accepted=0 AND username1= ?");
                    pstmt.setString(1, user1);
                    pstmt.setString(2, user2);
                    rs = pstmt.executeQuery();
                    if (rs.next()) {
                        return rs.getString(1);
                    } else {
                        return "OTHER";
                    }
                }
            } catch (SQLException ex) {
                return "OTHER";
            }
        } else {
            return "OTHER";
        }
    }

    //bij request -> username1 is de vragende, username2 de accepterende
    public String getPendingRelationBetweenAskedByUser1(String user1, String user2) {
        if (isConnected() && usernameExists(user2) && usernameExists(user1)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT type FROM Relations WHERE processed=0 AND username1= ? AND accepted=0 AND username2= ?");
                pstmt.setString(1, user1);
                pstmt.setString(2, user2);
                ResultSet rs = pstmt.executeQuery();
                if (rs.next()) {
                    return rs.getString(1);
                } else {
                    return "OTHER";
                }
            } catch (SQLException ex) {
                return "OTHER";
            }
        } else {
            return "OTHER";
        }
    }

    //bij request -> username1 is de vragende, username2 de accepterende
    public String getPendingRelationBetweenAskedByUser2(String user1, String user2) {
        if (isConnected() && usernameExists(user2) && usernameExists(user1)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT type FROM Relations WHERE processed=0 AND username2= ? AND accepted=0 AND username1= ?");
                pstmt.setString(1, user1);
                pstmt.setString(2, user2);
                ResultSet rs = pstmt.executeQuery();
                if (rs.next()) {
                    return rs.getString(1);
                } else {
                    return "OTHER";
                }
            } catch (SQLException ex) {
                return "OTHER";
            }
        } else {
            return "OTHER";
        }
    }

    public String getAcceptedRelationBetween(String user1, String user2) {
        if (isConnected() && usernameExists(user2) && usernameExists(user1)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT type FROM Relations WHERE accepted=1 AND username1= ? AND username2= ?");
                pstmt.setString(1, user1);
                pstmt.setString(2, user2);
                ResultSet rs = pstmt.executeQuery();
                if (rs.next()) {
                    return rs.getString(1);
                } else {
                    pstmt = conn.prepareStatement("SELECT type FROM Relations WHERE accepted=1 AND username2= ? AND username1= ?");
                    pstmt.setString(1, user1);
                    pstmt.setString(2, user2);
                    rs = pstmt.executeQuery();
                    if (rs.next()) {
                        return rs.getString(1);
                    } else {
                        return "OTHER";
                    }
                }
            } catch (SQLException ex) {
                return "OTHER";
            }
        } else {
            return "OTHER";
        }
    }

    public void removeRelation(String askingUser, String acceptingUser) {
        if (isConnected() && usernameExists(askingUser) && usernameExists(acceptingUser)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("DELETE FROM Relations WHERE username1= ? AND username2= ?");
                pstmt.setString(1, askingUser);
                pstmt.setString(2, acceptingUser);
                pstmt.executeUpdate();

                pstmt = conn.prepareStatement("DELETE FROM Relations WHERE username2= ? AND username1= ?");
                pstmt.setString(1, askingUser);
                pstmt.setString(2, acceptingUser);
                pstmt.executeUpdate();
            } catch (SQLException ex) {
            }
        }
    }

    public String[] getFriends(String user) {
        if (isConnected() && usernameExists(user)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT username1 FROM Relations WHERE username2= ? AND type=\"FRIEND\" AND accepted=1 UNION " +
                        "SELECT username2 AS username1 FROM Relations WHERE username1= ? AND type=\"FRIEND\" AND accepted=1");
                pstmt.setString(1, user);
                pstmt.setString(2, user);
                ResultSet rs = pstmt.executeQuery();
                String[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result = new String[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getString(1);
                        currentRow++;
                        if (rs.next()) {
                            rs.previous();
                            String[] temp = new String[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result = temp;
                        }
                    }
                }

                if (result == null) // Make sure it always returns an array
                {
                    result = new String[0];
                }
                return result;
            } catch (SQLException ex) {
                return new String[0];
            }
        } else {
            return new String[0];
        }
    }

    public String[] getFamily(String user) {
        if (isConnected() && usernameExists(user)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT username1 FROM Relations WHERE username2= ? AND type=\"FAMILY\" AND accepted=1 UNION " +
                        "SELECT username2 AS username1 FROM Relations WHERE username1= ? AND type=\"FAMILY\" AND accepted=1");
                pstmt.setString(1, user);
                pstmt.setString(2, user);
                ResultSet rs = pstmt.executeQuery();
                String[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result = new String[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getString(1);
                        currentRow++;
                        if (rs.next()) {
                            rs.previous();
                            String[] temp = new String[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result = temp;
                        }
                    }
                }

                if (result == null) // Make sure it always returns an array
                {
                    result = new String[0];
                }
                return result;
            } catch (SQLException ex) {
                return new String[0];
            }
        } else {
            return new String[0];
        }
    }

    public String[] getFriendRequests(String user) {
        if (isConnected() && usernameExists(user)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT username1 FROM Relations WHERE processed=0 AND username2= ? AND type=\"FRIEND\" AND accepted=0");
                pstmt.setString(1, user);
                ResultSet rs = pstmt.executeQuery();
                String[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result = new String[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getString(1);
                        currentRow++;
                        if (rs.next()) {
                            rs.previous();
                            String[] temp = new String[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result = temp;
                        }
                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new String[0];
                }
                return result;
            } catch (SQLException ex) {
                return new String[0];
            }
        } else {
            return new String[0];
        }
    }

    public String[] getFamilyRequests(String user) {
        if (isConnected() && usernameExists(user)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT username1 FROM Relations WHERE processed=0 AND username2= ? AND type=\"FAMILY\" AND accepted=0");
                pstmt.setString(1, user);
                ResultSet rs = pstmt.executeQuery();
                String[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result = new String[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getString(1);
                        currentRow++;
                        if (rs.next()) {
                            rs.previous();
                            String[] temp = new String[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result = temp;
                        }
                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new String[0];
                }
                return result;
            } catch (SQLException ex) {
                return new String[0];
            }
        } else {
            return new String[0];
        }
    }

    public void acceptRequest(String askingUser, String acceptingUser) {
        if (isConnected() && usernameExists(askingUser) && usernameExists(acceptingUser)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("UPDATE Relations SET accepted=1, processed=1 WHERE username1= ? AND username2= ?");
                pstmt.setString(1, askingUser);
                pstmt.setString(2, acceptingUser);
                pstmt.executeUpdate();
            } catch (SQLException ex) {
            }
        }
    }

    public void declineRequest(String askingUser, String acceptingUser) {
        if (isConnected() && usernameExists(askingUser) && usernameExists(acceptingUser)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("UPDATE Relations SET accepted=0, processed=1 WHERE username1= ? AND username2= ?");
                pstmt.setString(1, askingUser);
                pstmt.setString(2, acceptingUser);
                pstmt.executeUpdate();
            } catch (SQLException ex) {
            }
        }
    }

    public boolean makeRelationRequest(String askingUser, String acceptingUser, String relation) {
        if (isConnected() && usernameExists(askingUser) && usernameExists(acceptingUser)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Relations (username1, username2, type, accepted, processed, viewedByAskingUser) VALUES (? , ?,  ?, 0, 0, 0)");
                pstmt.setString(1, askingUser);
                pstmt.setString(2, acceptingUser);
                pstmt.setString(3, relation);
                pstmt.executeUpdate();
                return true;

            } catch (SQLException ex) {
                return false;
            }
        }
        return false;
    }

    public String[] getPendingFriendRequests(String user) {
        if (isConnected() && usernameExists(user)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT username2 FROM Relations WHERE viewedByAskingUser=0 AND username1= ? AND type=\"FRIEND\"");
                pstmt.setString(1, user);
                ResultSet rs = pstmt.executeQuery();
                String[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result = new String[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getString(1);
                        currentRow++;
                        if (rs.next()) {
                            rs.previous();
                            String[] temp = new String[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result = temp;
                        }
                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new String[0];
                }
                return result;
            } catch (SQLException ex) {
                return new String[0];
            }
        } else {
            return new String[0];
        }
    }

    public String[] getPendingFamilyRequests(String user) {
        if (isConnected() && usernameExists(user)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT username2 FROM Relations WHERE viewedByAskingUser=0 AND username1= ? AND type=\"FAMILY\"");
                pstmt.setString(1, user);
                ResultSet rs = pstmt.executeQuery();
                String[] result = null;
                int currentRow = 0;

                if (rs.next()) {
                    rs.previous();
                    result = new String[1];

                    while (rs.next()) {
                        result[currentRow] = rs.getString(1);
                        currentRow++;
                        if (rs.next()) {
                            rs.previous();
                            String[] temp = new String[currentRow + 1];
                            System.arraycopy(result, 0, temp, 0, result.length);
                            result = temp;
                        }
                    }
                }
                if (result == null) // Make sure it always returns an array
                {
                    result = new String[0];
                }
                return result;
            } catch (SQLException ex) {
                return new String[0];
            }
        } else {
            return new String[0];
        }
    }

    public void viewed(String user1, String user2) {
        if (isConnected() && usernameExists(user2) && usernameExists(user1)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("UPDATE Relations SET viewedByAskingUser=1 WHERE username1= ? AND username2= ?");
                pstmt.setString(1, user1);
                pstmt.setString(2, user2);
                pstmt.executeUpdate();

                pstmt = conn.prepareStatement("UPDATE Relations SET viewedByAskingUser=1 WHERE username2= ? AND username1= ?");
                pstmt.setString(1, user1);
                pstmt.setString(2, user2);
                pstmt.executeUpdate();
            } catch (SQLException ex) {
            }
        }
    }

    public boolean isAccepted(String user1, String user2) {
        if (isConnected() && usernameExists(user2) && usernameExists(user1)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT accepted FROM Relations WHERE ((username1= ? AND username2= ?) OR (username1= ? AND username2= ?))");
                pstmt.setString(1, user1);
                pstmt.setString(2, user2);
                pstmt.setString(3, user2);
                pstmt.setString(4, user1);
                ResultSet rs = pstmt.executeQuery();
                if (rs.next()) {
                    return rs.getInt(1) == 1;
                } else {
                    return false;
                }
            } catch (SQLException ex) {
                return false;
            }
        } else {
            return false;
        }
    }

    public boolean isProcessed(String user1, String user2) {
        if (isConnected() && usernameExists(user2) && usernameExists(user1)) {
            try {
                PreparedStatement pstmt = conn.prepareStatement("SELECT processed FROM Relations WHERE ((username1= ? AND username2= ?) OR (username1= ? AND username2= ?))");
                pstmt.setString(1, user1);
                pstmt.setString(2, user2);
                pstmt.setString(3, user2);
                pstmt.setString(4, user1);
                ResultSet rs = pstmt.executeQuery();
                if (rs.next()) {
                    return rs.getInt(1) == 1;
                } else {
                    return false;
                }
            } catch (SQLException ex) {
                return false;
            }
        } else {
            return false;
        }
    }
}
